package com.threeti.mecool.legacy.infrastructure.persistence;

import java.util.List;

import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;

import com.threeti.mecool.legacy.domain.model.basicdata.AdminDivisionLegacy;

public interface AdminDivisionLegacyRepository extends JpaRepository<AdminDivisionLegacy, String> {
  
  @Query("from AdminDivisionLegacy where deep = ?1 order by code")
  List<AdminDivisionLegacy> findByDeep(int deep);
  
  @Query("from AdminDivisionLegacy where parentCityAdName = ?1 order by code")
  List<AdminDivisionLegacy> findByParentCityAdNameOrderByCode(String parentCityAdName);
  
  @Query("from AdminDivisionLegacy where parentProvinceAdName = ?1 and deep = ?2 order by code")
  List<AdminDivisionLegacy> findByParentProvinceAdNameAndDeepOrderByCode(String parentProvinceAdName, int deep);
  
  /** leads mutli records exception whenever meets 市辖区,so this method should only be used for testing purpose */
  //@Query("select distinct ad from AdminDivisionLegacy ad where ad.name = ?1")
  @Deprecated 
  AdminDivisionLegacy findByName(String adName);

  AdminDivisionLegacy findById(Long adId);
  
  AdminDivisionLegacy findByCode(String code);
  
  @Query(nativeQuery=true,value="select t.ad_id from MD_ADMIN_DIV t where t.province_ad_name=?1 ")
  List<String> findAdIdByParentProvinceAdName(String provinceAdName);
  
  /**
   * 
   * @author Yuqi Chou
   * @param provinceAdName
   * @param cityAdName
   * @param cityAdNameLike	操
   * @return
   * @version Aug 30, 2012  12:11:41 PM
   */
  @Query(nativeQuery=true,value=" select t.ad_id "+
		  						"   from MD_ADMIN_DIV t"+
								"  where t.province_ad_name = :provinceAdName "+
								"    and (t.city_ad_name = :cityAdName or t.city_ad_name like :cityAdNameLike )"+
								"     or (t.ad_name = :cityAdName and t.deep = 2)")
  List<String> findAdIdByParentProvinceAdNameAndCityAdName(@Param("provinceAdName") String provinceAdName,@Param("cityAdName") String cityAdName,@Param("cityAdNameLike") String cityAdNameLike);
  
  
  @Query(nativeQuery=true,value="select t.ad_id from MD_ADMIN_DIV t where t.province_ad_name=?1 and t.city_ad_name=?2 and t.ad_name=?3")
  List<String> findAdIdByParentProvinceAdNameAndCityAdNameAndAdName(String provinceAdName,String cityAdName,String adName);
}
